package com.qf.lja.dao;

import com.qf.lja.enity.Product;
import com.qf.lja.util.JDBCUtil;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {

    public List<Product> getProductsPage(int currenPage, int pageSize) {
        String sql = "SELECT * FROM `product` WHERE `status` = 1 LIMIT ? , ?";
        int offset = (currenPage - 1) * pageSize;

        JDBCUtil jdbcUtil = new JDBCUtil();

        ResultSet rs = jdbcUtil.query(sql, offset, pageSize);
        List<Product> list = new ArrayList<>();
        try {
            while (rs.next()) {//循环遍历所以行
                Product product = convertToProduct(rs);
                //省略其他属性
                list.add(product);
            }
        } catch (SQLException e) {
            System.out.println("sql执行失败！");
            e.printStackTrace();
        } finally {
            jdbcUtil.closeConnection();
        }
        return list;
    }

    public int countProducts() {
        String sql = "select count(*) from product";


        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql);

        int rows = 0;
        try {
            if (rs.next()) {
                rows = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.closeConnection();
        }
        return rows;
    }


    public List<Product> queryTop10PopularProducts() {
        String sql = "SELECT * FROM `product` WHERE `status` = 1 ORDER BY `popularity_index` DESC LIMIT 8";


        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql);
        List<Product> popularProducts = new ArrayList<>();
        try {
            while (rs.next()) {
                Product product = convertToProduct(rs);
                if (popularProducts.size() <= 7 && product.getStatus() == 1) {
                    popularProducts.add(product);
                }
            }
        } catch (SQLException e) {
            System.out.println("SQL执行失败！");
            e.printStackTrace();
        }
        jdbcUtil.closeConnection();
        return popularProducts;
    }

    //查询所有的product
    public List<Product> queryAllProductr() {

        String sql = "SELECT * FROM `product` WHERE `status` = 1";
        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql);
        List<Product> productlist = new ArrayList<>();
        try {
            while (rs.next()) {//循环遍历所以行
                Product product = convertToProduct(rs);
                //省略其他属性
                productlist.add(product);
            }
        } catch (SQLException e) {
            System.out.println("sql执行失败！");
            e.printStackTrace();
        }
        jdbcUtil.closeConnection();
        return productlist;
    }


    public Product findByUsernameAndpasswd(String username, String password) {
        String sql = "SELECT * FROM product WHERE user_name = ? AND  user_pass = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql, username, password);
        try {
            if (rs.next()) {
                Product product = convertToProduct(rs);
                return product;
            }
        } catch (SQLException e) {
            System.out.println("sql执行失败！");
            e.printStackTrace();
        }

        return null;
    }


    public Product findByProductId(int productId) {
        String sql = "select * from product where product_id=?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql, productId);
        try {
            if (rs.next()) {
                return convertToProduct(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    private Product convertToProduct(ResultSet rs) {
        try {
            Product product = new Product();
            product.setProductId(rs.getInt("product_id"));
            product.setProductName(rs.getString("product_name"));
            product.setPrice(rs.getDouble("price"));
            product.setLaunchDate(rs.getString("launch_date")); // Assuming launch_date is stored as String
            product.setImageUrl(rs.getString("image_url"));
            product.setPopularityIndex(rs.getInt("popularity_index"));
            product.setStatus(rs.getInt("status"));
            product.setInventory(rs.getInt("inventory"));
            product.setDescription(rs.getString("description"));
            product.setModifiedAt(rs.getString("modified_at"));
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean addProduct(String product_name, BigDecimal price, String image_url,
                              Integer popularity_index, Integer inventory, Integer status,
                              String description) {
        String sql = "INSERT INTO product " +
                "(product_name, price, image_url, popularity_index, inventory, status, " +
                "description, modified_at, launch_date) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())";

        JDBCUtil jdbcUtil = new JDBCUtil(); // 假设 JDBCUtil 是你封装的 JDBC 工具类
        boolean success = jdbcUtil.insert(sql, product_name, price, image_url,
                popularity_index, inventory, status,
                description);

        if (success) {
            System.out.println("产品添加成功！");
        } else {
            System.out.println("产品添加失败！");
        }

        return success;
    }

    public boolean deleteUserById(int userId) {
        // 组织 SQL 删除语句
        String sql = "DELETE FROM user WHERE user_id = ?";

        // 调用 prepareUpdate 方法执行 SQL 删除操作
        JDBCUtil dbUtil = new JDBCUtil();
        boolean success = dbUtil.insert(sql, userId);

        // 检查是否成功删除数据
        if (success) {
            System.out.println("成功删除用户，用户ID：" + userId);
            return true;
        } else {
            System.out.println("删除用户失败，请检查控制台。");
            return false;
        }
    }

    public boolean update(int userId, String username, String password, String role, String state) {
        String sql = "UPDATE user SET username=?, password=?, role=?, state=? WHERE user_id=?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        int rs = jdbcUtil.update(sql, username, password, role, state, userId);
        if (rs != -1) {
            System.out.println("update success !!!!! ");
        } else {
            System.out.println("update error !!!");
        }
        return true;
    }

    public List<Product> findLike(String search) {
        //在product表中模糊查询，（`product_name`，`description`）中like
        System.out.println("sea" + search);
        String sql = "SELECT * FROM `product` WHERE `product_name` LIKE ? OR `description` LIKE ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql, "%" + search + "%", "%" + search + "%");
        List<Product> productlist = new ArrayList<>();
        try {
            while (rs.next()) {//循环遍历所以行
                Product product = convertToProduct(rs);
                //省略其他属性
                if (product.getStatus() == 1) {
                    productlist.add(product);
                }
            }
            return productlist;
        } catch (SQLException e) {
            System.out.println("sql执行失败！");
            e.printStackTrace();
        }
        jdbcUtil.closeConnection();
        return productlist;
    }

    public void updateStock(int pId, int iNum) {
        String sql = "UPDATE product SET inventory = inventory - ? WHERE product_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        jdbcUtil.update(sql, iNum, pId);
        jdbcUtil.closeConnection();
    }

    public void updateStatus(int p_id, int status) {
        String sql = "UPDATE product SET status = ? WHERE product_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        jdbcUtil.update(sql, status == 1 ? 0 : 1, p_id);
        jdbcUtil.closeConnection();
    }


    //卖家端，不需要限制
    public List<Product> SeleteAll() {
        String sql = "SELECT * FROM product";
        JDBCUtil jdbcUtil = new JDBCUtil();
        ResultSet rs = jdbcUtil.query(sql);
        List<Product> productlist = new ArrayList<>();
        try {
            while (rs.next()) {
                Product product = convertToProduct(rs);
                //省略其他属性
                productlist.add(product);
            }
            return productlist;
        } catch (SQLException e) {
            System.out.println("sql执行失败！");
            e.printStackTrace();
        }
        jdbcUtil.closeConnection();
        return productlist;
    }

    public boolean deleteUserByPId(int i) {
        String sql = "DELETE FROM product WHERE product_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        return jdbcUtil.insert(sql, i);
    }


    public void updateGoodstatus(String oId, int i) {
        String sql = "UPDATE buyr_order SET order_status = ? WHERE order_id = ? ";
        JDBCUtil jdbcUtil = new JDBCUtil();
        jdbcUtil.update(sql, i, oId);
        jdbcUtil.closeConnection();
    }

    public void updatemp(String price, String inventory, String product_id) {
        String sql = "UPDATE product SET price = ?, inventory = ? WHERE product_id = ?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        jdbcUtil.update(sql, Double.valueOf(price), Integer.parseInt(inventory), Integer.parseInt(product_id));
        jdbcUtil.closeConnection();
    }
}






